import pandas as pd

def main():
    # 设置文件路径（请根据实际情况修改）
    # 注意：使用正斜杠或在路径前加r
    file_path = r'C:\Users\admin\Desktop\zsh\student.xlsx'  # 请确保文件在此路径下

    try:
        # 读取Excel文件
        print("正在读取文件...\n")
        df = pd.read_excel(file_path, sheet_name='Sheet1')

        # 1. 打印文件内容
        print("="*50)
        print("1. student.xlsx文件内容：")
        print("="*50)
        print(df.to_string(index=False))  # 不显示索引列
        print("\n" + "="*50 + "\n")

        # 2. 按性别统计
        print("2. 按性别统计学生人数：")
        print("-"*30)
        gender_counts = df['性别'].value_counts()
        for gender, count in gender_counts.items():
            print(f"{gender}生：{count}人")
        print("\n" + "="*50 + "\n")

        # 3. 按年龄段统计
        print("3. 按年龄段统计学生人数：")
        print("-"*30)
        bins = [0, 18, 30, 60, float('inf')]
        labels = ['0-18岁', '19-30岁', '31-60岁', '61岁及以上']
        df['年龄段'] = pd.cut(df['年龄'], bins=bins, labels=labels)
        age_counts = df['年龄段'].value_counts().sort_index()  # 按年龄段排序
        for age_group, count in age_counts.items():
            print(f"{age_group}：{count}人")
        print("\n" + "="*50 + "\n")

        # 4. 按地区统计
        print("4. 按地区统计学生人数：")
        print("-"*30)
        def extract_region(address):
            if '海南省' in address:
                # 海南省提取市县
                parts = address.split('省')[1].split('市')
                if len(parts) > 1:
                    return parts[0] + '市'
                parts = address.split('省')[1].split('县')
                if len(parts) > 1:
                    return parts[0] + '县'
                return parts[0]
            else:
                # 外省提取省份
                for keyword in ['省', '自治区', '市']:
                    if keyword in address:
                        return address.split(keyword)[0] + keyword
                return address

        df['地区'] = df['家庭地址'].apply(extract_region)
        region_counts = df['地区'].value_counts()
        for region, count in region_counts.items():
            print(f"{region}：{count}人")
        print("\n" + "="*50)

    except FileNotFoundError:
        print(f"错误：找不到文件 '{file_path}'")
        print("请检查文件路径是否正确，文件名是否为'student.xlsx'")
    except ImportError as e:
        if 'openpyxl' in str(e):
            print("错误：缺少openpyxl库")
            print("请在终端中运行以下命令安装：")
            print("C:/Users/admin/anaconda3/envs/myenv/python.exe -m pip install openpyxl")
        else:
            print(f"导入错误：{e}")
    except Exception as e:
        print(f"发生错误：{str(e)}")

if __name__ == '__main__':
    main()
    
